package cn.jhc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.jhc.bean.AllHfmsgBean;
import cn.jhc.bean.HfmsgBean;
import cn.jhc.bean.MsgBean;

public class NewMsgDAO {
	private Connection con = null;
	
	int total;
	int PageCount; 
	int PageHfCount;
	int MsgCount;

	public void addMessage(int userid, String title, String content) {
		String sql = "insert into newsmessage(use_id,title,content,date,essence) "
				+ "values(?,?,?,now(),'no')";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, userid);
			st.setString(2, title);
			st.setString(3, content);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void addTopTitle(int userid, String title, String content) {
		String sql = "insert into newsmessage(use_id,title,content,date,essence) "
				+ "values(?,?,?,now(),'top')";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, userid);
			st.setString(2, title);
			st.setString(3, content);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void addHfMessage(int userid, String content, int titleid) {
		String sql = "insert into hfnewsmessage(use_id,content,title_id,date) "
				+ "values(?,?,?,now())";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, userid);
			st.setString(2, content);
			st.setInt(3, titleid);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public ArrayList<MsgBean> getAllMessages(int page,int titleCount) {
		ArrayList<MsgBean> list = new ArrayList<MsgBean>();
		String sql = "select u.id,u.displayname,a.id,a.title,a.content,a.date,a.essence from user as u "
				+ "join (select m.id,m.use_id,m.title,m.content,m.date,m.essence "
				+"from newsmessage as m where m.essence='no')as a where u.id=a.use_id order by a.id desc limit "
				+ (page-1) * (titleCount) + "," + titleCount + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);	
			while (result.next()) {
				MsgBean m = new MsgBean();
				m.setUserID(result.getInt(1));
				m.setDisplayname(result.getString(2));
				m.setId(result.getInt(3));
				m.setTitle(result.getString(4));
				m.setContent(result.getString(5));
				m.setDate(result.getDate(6));
				m.setEssence(result.getString(7));
				list.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public ArrayList<MsgBean> getTopMessages() {
		ArrayList<MsgBean> list = new ArrayList<MsgBean>();
		String sql = "select u.id,u.name,a.id,a.title,a.content,a.date,a.essence from adminuser as u "
				+ "join (select m.id,m.use_id,m.title,m.content,m.date,m.essence "
				+"from newsmessage as m where m.essence='top')as a where u.id=a.use_id order by a.id desc";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			while (result.next()) {
				MsgBean m = new MsgBean();
				m.setUserID(result.getInt(1));
				m.setDisplayname(result.getString(2));
				m.setId(result.getInt(3));
				m.setTitle(result.getString(4));
				m.setContent(result.getString(5));
				m.setDate(result.getDate(6));
				m.setEssence(result.getString(7));
				list.add(m);
			}
		} catch (SQLException e) {
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public ArrayList<MsgBean> getBetterMessages() {
		ArrayList<MsgBean> list = new ArrayList<MsgBean>();
		String sql = "select u.id,u.displayname,a.id,a.title,a.content,a.date,a.essence from user as u "
				+ "join (select m.id,m.use_id,m.title,m.content,m.date,m.essence "
				+"from newsmessage as m where m.essence='yes')as a where u.id=a.use_id order by a.id desc";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			while (result.next()) {
				MsgBean m = new MsgBean();
				m.setUserID(result.getInt(1));
				m.setDisplayname(result.getString(2));
				m.setId(result.getInt(3));
				m.setTitle(result.getString(4));
				m.setContent(result.getString(5));
				m.setDate(result.getDate(6));
				m.setEssence(result.getString(7));
				list.add(m);
			}
		} catch (SQLException e) {
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<AllHfmsgBean> getHFMessages(int id,int page) {
		ArrayList<AllHfmsgBean> List = new ArrayList<AllHfmsgBean>();
		String sql = "select u.id,u.displayname,h.title_id,h.content,h.date"
				+ " from user as u join hfnewsmessage as h where h.title_id=" + id
				+ " and u.id=h.use_id limit "
				+ (page-1) * (10) + "," + 10 + "";
		Statement stat = null;
		ResultSet result = null;
		try {
			stat = con.createStatement();
			result = stat.executeQuery(sql);
			while (result.next()) {
				AllHfmsgBean h = new AllHfmsgBean();
				h.setUserId(result.getInt(1));
				h.setDisplayname(result.getString(2));
				h.setTitleid(result.getInt(3));
				h.setContent(result.getString(4));
				h.setDate(result.getDate(5));
				List.add(h);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return List;
	}
	
	public HfmsgBean getoneMessages(int id) {
		HfmsgBean m = new HfmsgBean();
		String sql = "select u.id,u.displayname,m.id,m.title,m.content,m.date"
				+ " from user as u join newsmessage as m where m.id=? and u.id = m.use_id";
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeQuery();
			if (!result.next())
				m = null;
			else {
				m.setUserID(result.getInt(1));
				m.setDisplayname(result.getString(2));
				m.setId(result.getInt(3));
				m.setTitle(result.getString(4));
				m.setContent(result.getString(5));
				m.setDate(result.getDate(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return m;
	}
	
	public HfmsgBean getonetopMessages(int id) {
		HfmsgBean m = new HfmsgBean();
		String sql = "select a.id,a.name,m.id,m.title,m.content,m.date"
				+ " from adminuser as a join newsmessage as m where m.id=? and a.id = m.use_id";
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeQuery();
			if (!result.next())
				m = null;
			else {
				m.setUserID(result.getInt(1));
				m.setDisplayname(result.getString(2));
				m.setId(result.getInt(3));
				m.setTitle(result.getString(4));
				m.setContent(result.getString(5));
				m.setDate(result.getDate(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return m;
	}

	public int getCount(int titleCount) {
		String sql = "select count(*) from user as u join newsmessage as m on u.id = m.use_id and essence='no'";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%titleCount==0){
					this.PageCount=(result.getInt(1)/titleCount);
				}else{
					this.PageCount=(result.getInt(1)/titleCount+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageCount ;
	}
	public int getHfCount(int id) {
		String sql = "select count(*) from user as u join hfnewsmessage as h on u.id = h.use_id where h.title_id=" + id+"";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%10==0){
					this.PageHfCount=(result.getInt(1)/10);	
				}else{
					this.PageHfCount=(result.getInt(1)/10+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageHfCount ;
	}
	public int getMsgCount(int id) {
		String sql = "select count(*) from hfnewsmessage where title_id =? ";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				this.MsgCount=(result.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return MsgCount ;
	}
	public void deleteTitle(int id) {
		String sql = "delete from newsmessage where id = ?";

		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void deleteHfTitle(int id) {
		String sql = "delete from hfnewsmessage where title_id = ?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void updateEssence(int id) {
		String sql = "update newsmessage set essence='yes' where id = ?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void updateNo(int id) {
		String sql = "update newsmessage set essence='no' where id = ?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void setCon(Connection con) {
		this.con = con;
	}
}
